﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class CheckReceiptManager:ConnectionManager
    {

        #region Adding/Updating
        public SqlException AddNewCheckEntry(CheckReceiptUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO CHECK_RECEIPT (DocNum, Reference, Remarks, DocType, ReceiptNumber, PostingDate, TINNumber, IsAccount, PayeeCode, PayeeName, AddressOrRemarks, BankName, CheckNumber, CheckDate, CheckAmount, CheckStatus, DocStatus, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES (@DocNum, @Reference, @Remarks, @DocType, @ReceiptNumber, @PostingDate, @TINNumber, @IsAccount, @PayeeCode, @PayeeName, @AddressOrRemarks, @BankName, @CheckNumber, @CheckDate, @CheckAmount, @CheckStatus, @DocStatus, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@DocType", oUnit.DocumentType));
                oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", oUnit.ReceiptNumber));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@TINNumber", oUnit.TINNumber));
                oCommand.Parameters.Add(new SqlParameter("@IsAccount", oUnit.IsAccount));
                oCommand.Parameters.Add(new SqlParameter("@PayeeCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@PayeeName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@AddressOrRemarks", oUnit.Address));

                oCommand.Parameters.Add(new SqlParameter("@BankName", oUnit.BankName));
                oCommand.Parameters.Add(new SqlParameter("@CheckNumber", oUnit.CheckNumber));
                oCommand.Parameters.Add(new SqlParameter("@CheckDate", oUnit.CheckDate));
                oCommand.Parameters.Add(new SqlParameter("@CheckAmount", oUnit.CheckAmount));
                oCommand.Parameters.Add(new SqlParameter("@CheckStatus", oUnit.CheckStatus));
                //CheckNumber, CheckDate, CheckAmount, CheckStatus,
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();
                
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateCheckEntry(CheckReceiptUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE CHECK_RECEIPT SET Reference=@Reference, Remarks=@Remarks, DocType=@DocType, ReceiptNumber=@ReceiptNumber, PostingDate=@PostingDate, TINNumber=@TINNumber, IsAccount=@IsAccount, PayeeCode=@PayeeCode, PayeeName=@PayeeName, AddressOrRemarks=@AddressOrRemarks, BankName=@BankName, CheckNumber=@CheckNumber, CheckDate=@CheckDate, CheckAmount=@CheckAmount, CheckStatus=@CheckStatus, DocStatus=@DocStatus, DateModified=@DateModified, ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@DocType", oUnit.DocumentType));
                oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", oUnit.ReceiptNumber));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@TINNumber", oUnit.TINNumber));
                oCommand.Parameters.Add(new SqlParameter("@IsAccount", oUnit.IsAccount));
                oCommand.Parameters.Add(new SqlParameter("@PayeeCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@PayeeName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@AddressOrRemarks", oUnit.Address));

                oCommand.Parameters.Add(new SqlParameter("@BankName", oUnit.BankName));
                oCommand.Parameters.Add(new SqlParameter("@CheckNumber", oUnit.CheckNumber));
                oCommand.Parameters.Add(new SqlParameter("@CheckDate", oUnit.CheckDate));
                oCommand.Parameters.Add(new SqlParameter("@CheckAmount", oUnit.CheckAmount));
                oCommand.Parameters.Add(new SqlParameter("@CheckStatus", oUnit.CheckStatus));

                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion

        public DataTable GetCheckEntry(string DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CHECK_RECEIPT WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocNum));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseCheckEntryByDocument(string _DocNum)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _DocNum = '%' + _DocNum.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CHECK_RECEIPT WHERE DocNum LIKE @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNum));


            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseCheckEntryByReceiptNumner(string _DocumentType, string _ReceiptNumber)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _ReceiptNumber = '%' + _ReceiptNumber.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CHECK_RECEIPT WHERE DocType=@DocType AND ReceiptNumber LIKE @ReceiptNumber";
            oCommand.Parameters.Add(new SqlParameter("@ReceiptNumber", _ReceiptNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocType", _DocumentType));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseCheckEntryByCardName(string _CardName)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _CardName = '%' + _CardName.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CHECK_RECEIPT WHERE CardName LIKE @CardName";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseCheckEntryByRemarks(string _Remarks)
        {
            DataTable dt = new DataTable();
            SqlCommand oCommand = new SqlCommand();

            _Remarks = '%' + _Remarks.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CHECK_RECEIPT WHERE Remarks LIKE @Remarks";
            oCommand.Parameters.Add(new SqlParameter("@Remarks", _Remarks));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public SqlException PostDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE CHECK_RECEIPT SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }

        public SqlException CloseDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE CHECK_RECEIPT SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException CancelDocument(string _DOCUMENT_NUMBER)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE CHECK_RECEIPT SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCUMENT_NUMBER));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

    }
}
